#! /bin/bash
HIVE_HOME=/usr/bin/hive

Last_Month=$(date -d "$(date +%Y%m)01 last month" +%Y-%m-01)
${HIVE_HOME} -S -e "
--分区
SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions.pernode=10000;
set hive.exec.max.dynamic.partitions=100000;
set hive.exec.max.created.files=150000;
--hive压缩
set hive.exec.compress.intermediate=true;
set hive.exec.compress.output=true;
--写入时压缩生效
set hive.exec.orc.compression.strategy=COMPRESSION;
--分桶
set hive.enforce.bucketing=true;
set hive.enforce.sorting=true;
set hive.optimize.bucketmapjoin = true;
set hive.auto.convert.sortmerge.join=true;
set hive.auto.convert.sortmerge.join.noconditionaltask=true;
--并行执行
set hive.exec.parallel=true;
set hive.exec.parallel.thread.number=8;
--矢量化查询
set hive.vectorized.execution.enabled=true;
--关联优化器
set hive.optimize.correlation=true;
--读取零拷贝
set hive.exec.orc.zerocopy=true;
--join数据倾斜
set hive.optimize.skewjoin=true;
-- set hive.skewjoin.key=100000;
set hive.optimize.skewjoin.compiletime=true;
set hive.optimize.union.remove=true;
-- group倾斜
set hive.groupby.skewindata=true;

insert into itcast_dws.customer_signup_dws PARTITION (payment_time_year, payment_time_month, payment_time_day)
select
    count(t1.customer_id) as signup_num,
    itcast_school_id,
    itcast_school_name,
    itcast_subject_id,
    itcast_subject_name,
    '-1' as tdepart_id,
    '-1' as tdepart_name,
    '-1' as origin_type,
    origin_type_stat,
    payment_time_hour,
    '1' as groupType,
    payment_time_year,
    payment_time_month, 
    payment_time_day
    from itcast_dwm.customer_signup_dwm t1 
    where CONCAT_WS('-',t1.payment_time_year,t1.payment_time_month,t1.payment_time_day) >= '$Last_Month'
    group by itcast_school_id,itcast_school_name,itcast_subject_id,itcast_subject_name,
    origin_type_stat,payment_time_hour,payment_time_year,payment_time_month, payment_time_day;


insert into itcast_dws.customer_signup_dws PARTITION(payment_time_year, payment_time_month, payment_time_day)
select
    count(t1.customer_id) as signup_num,
    '-1' as itcast_school_id,
   '-1' as  itcast_school_name,
    '-1' as itcast_subject_id,
   '-1' as  itcast_subject_name,
    '-1' as tdepart_id,
    '-1' as tdepart_name,
    origin_type,
    origin_type_stat,
    payment_time_hour,
    '2' as groupType,
    payment_time_year,
    payment_time_month, 
    payment_time_day
    from itcast_dwm.customer_signup_dwm t1 
    where CONCAT_WS('-',t1.payment_time_year,t1.payment_time_month,t1.payment_time_day) >= '$Last_Month'
    group by origin_type,
    origin_type_stat,payment_time_hour,payment_time_year,payment_time_month, payment_time_day;

insert into itcast_dws.customer_signup_dws PARTITION (payment_time_year, payment_time_month, payment_time_day)
select
    count(t1.customer_id) as signup_num,
    '-1' as itcast_school_id,
   '-1' as  itcast_school_name,
    '-1' as itcast_subject_id,
   '-1' as  itcast_subject_name,
   tdepart_id,
    tdepart_name,
   '-1' as  origin_type,
    origin_type_stat,
    payment_time_hour,
    '3' as groupType,
    payment_time_year,
    payment_time_month, 
    payment_time_day
    from itcast_dwm.customer_signup_dwm t1 
    where CONCAT_WS('-',t1.payment_time_year,t1.payment_time_month,t1.payment_time_day) >= '$Last_Month'
    group by tdepart_id, tdepart_name,
    origin_type_stat,payment_time_hour,payment_time_year,payment_time_month, payment_time_day;

insert into itcast_dws.customer_signup_dws PARTITION (payment_time_year, payment_time_month, payment_time_day)
select
    count(t1.customer_id) as signup_num,
    '-1' as itcast_school_id,
   '-1' as  itcast_school_name,
    '-1' as itcast_subject_id,
   '-1' as  itcast_subject_name,
    '-1' as tdepart_id,
    '-1' as tdepart_name,
    '-1' as origin_type,
    origin_type_stat,
    payment_time_hour,
    '4' as groupType,
    payment_time_year,
    payment_time_month, 
    payment_time_day
    from itcast_dwm.customer_signup_dwm t1 
    where CONCAT_WS('-',t1.payment_time_year,t1.payment_time_month,t1.payment_time_day) >= '$Last_Month'
    group by  origin_type_stat,payment_time_hour,payment_time_year,payment_time_month, payment_time_day;